package com.CMUBiS.DB;

import java.nio.ByteBuffer;
import java.util.*;
import java.sql.Connection;
import java.text.*;

import org.apache.cassandra.thrift.Column;
import org.apache.cassandra.thrift.ColumnParent;
import org.apache.cassandra.thrift.SlicePredicate;


import me.prettyprint.*;
import me.prettyprint.cassandra.model.BasicColumnDefinition;
import me.prettyprint.cassandra.model.BasicColumnFamilyDefinition;
import me.prettyprint.cassandra.model.IndexedSlicesQuery;
import me.prettyprint.cassandra.serializers.ByteBufferSerializer;
import me.prettyprint.cassandra.serializers.DateSerializer;
import me.prettyprint.cassandra.serializers.IntegerSerializer;
import me.prettyprint.cassandra.serializers.LongSerializer;
import me.prettyprint.cassandra.serializers.SerializerTypeInferer;
import me.prettyprint.cassandra.serializers.StringSerializer;
import me.prettyprint.cassandra.service.ThriftCfDef;
import me.prettyprint.cassandra.service.template.ColumnFamilyResult;
import me.prettyprint.hector.api.*;
import me.prettyprint.hector.api.beans.ColumnSlice;
import me.prettyprint.hector.api.beans.HColumn;
import me.prettyprint.hector.api.beans.OrderedRows;
import me.prettyprint.hector.api.beans.Row;
import me.prettyprint.hector.api.beans.Rows;
import me.prettyprint.hector.api.ddl.ColumnFamilyDefinition;
import me.prettyprint.hector.api.ddl.ComparatorType;
import me.prettyprint.hector.api.ddl.KeyspaceDefinition;
import me.prettyprint.hector.api.factory.HFactory;
import me.prettyprint.hector.api.mutation.Mutator;
import me.prettyprint.hector.api.query.ColumnQuery;
import me.prettyprint.hector.api.query.QueryResult;
import me.prettyprint.hector.api.query.RangeSlicesQuery;
import me.prettyprint.hector.api.query.SliceQuery;

import com.CMUBiS.util.Address;
import com.CMUBiS.util.Bid;
import com.CMUBiS.util.Category;
import com.CMUBiS.util.Image;
import com.CMUBiS.util.Item;
import com.CMUBiS.util.User;
/**
 * 
 * @author Andy (andrewtu@cmu.edu, turner.andy@gmail.com)
 * @version 0.1
 * @since 0.1
 *
 */
public class CassandraDBQuery extends DBQuery{
	protected String url = null;
	protected String driver = null;
	protected String username = null; 
	protected String password = null;
	protected boolean useConnectionPool = true;
	protected int maxSQLConnections = 200;
	
	
	protected String keyspace = null;
	protected String host_port = null;
	protected StringSerializer se = StringSerializer.get();
	protected Keyspace ko = null;
	protected String CMUBiS = "CMUBiS";
	//TODO: update this entire file with trying connections again and throwing errors
	
	public CassandraDBQuery(){
		super();
		this.init();
	}
	
	// This should be read from a file
	protected void init(){
		this.keyspace = "CMUBiS";
		this.host_port = "10.22.0.102:9160";
		Cluster c = HFactory.getOrCreateCluster("CMUBiS", this.host_port);
		ko = HFactory.createKeyspace(this.keyspace, c);
		
		
		//this.makeNewAuthToken(150);
		//this.checkUsernamePassword("1","temp4now");
	}
	
	public boolean updateUser(String userid, String username, String password, String email, String firstName, String lastName){
		System.out.println(userid+" : " +username+" : " +password+" : " +email+" : " +firstName+" : " +lastName+" : ");
		try{
			Mutator<String> mutator = HFactory.createMutator(ko, se);
			mutator.addInsertion(username, "users",HFactory.createColumn("userid", Integer.parseInt(userid),se,IntegerSerializer.get()))
					.addInsertion(username, "users", HFactory.createStringColumn("password", password))
					.addInsertion(username, "users", HFactory.createStringColumn("email", email))
					.addInsertion(username, "users", HFactory.createStringColumn("firstname", firstName))
					.addInsertion(username, "users", HFactory.createStringColumn("lastname", lastName));
			mutator.execute();
		}catch(Exception e){System.out.println(e.getMessage());return false;}
		return true;
	}
	
	public float insertBid(int userID, int itemID, int quantity, float bid, float maxBid){
		try{
			
			SliceQuery<String, String, Integer> q = HFactory.createSliceQuery(ko, se, se,IntegerSerializer.get());
			q.setColumnFamily("items")
				.setKey(Integer.toString(itemID))
				.setColumnNames("quantity");
			QueryResult<ColumnSlice<String, Integer>> r = q.execute();
			
			Integer itemQuantity=(r.get().getColumnByName("quantity").getValue());
			System.out.println(itemQuantity);
			
			SliceQuery<String, String, String> q1 = HFactory.createSliceQuery(ko, se, se,se);
			q1.setColumnFamily("items")
				.setKey(Integer.toString(itemID))
				.setColumnNames("currentBid","maxBid");
			QueryResult<ColumnSlice<String, String>> r1 = q1.execute();
			
			Float curBid=Float.parseFloat(r1.get().getColumnByName("currentBid").getValue());
			Float curMaxBid=Float.parseFloat(r1.get().getColumnByName("maxBid").getValue());
			
			if(curBid==null){
				curBid=new Float(0);				
			}
			if(curMaxBid==null){
				curMaxBid=new Float(0);				
			}		
			if(itemQuantity<=quantity){
				if(maxBid>curMaxBid){
					Float actualBid=(float) Math.min(curMaxBid+.01,maxBid);
					System.out.println(actualBid);
					
					Mutator<String> mutator = HFactory.createMutator(ko, se);
					mutator.addInsertion(""+itemID, "items",HFactory.createStringColumn("currentBid", ""+actualBid));
					mutator.addInsertion(""+itemID, "items",HFactory.createStringColumn("maxBid", ""+maxBid));
					mutator.addInsertion(""+itemID, "items",HFactory.createColumn("bidderID", userID, se, IntegerSerializer.get()));
					
					Random generator = new Random();
					Integer curItem = new Integer(generator.nextInt());
					
					mutator.addInsertion(""+curItem, "bids",HFactory.createColumn("itemid",itemID, se, IntegerSerializer.get()));
					mutator.addInsertion(""+curItem, "bids",HFactory.createColumn("userid",userID, se, IntegerSerializer.get()));
					mutator.addInsertion(""+curItem, "bids", HFactory.createColumn("quantity",quantity, se, IntegerSerializer.get()));
					mutator.addInsertion(""+curItem, "bids", HFactory.createColumn("datetime", new Date().getTime(), se, LongSerializer.get()));
					mutator.addInsertion(""+curItem, "bids",HFactory.createStringColumn("curBid", ""+actualBid));
					mutator.addInsertion(""+curItem, "bids",HFactory.createStringColumn("maxBid", ""+curMaxBid));
					mutator.execute();	
					
					return actualBid;
				
				}else{
					Mutator<String> mutator = HFactory.createMutator(ko, se);
					mutator.addInsertion(Integer.toString(itemID), "bids",HFactory.createStringColumn("maxBid", ""+maxBid));
					mutator.execute();
					return -1;
				}
			}else{
			}		
		}catch(Exception e){
			System.out.print(e.getMessage());
		}
		return -1;
	}
	
	public boolean buyItemNow(int userID, int itemID, int quantity, int accountID){
		
		try{
			SliceQuery<String, String, Integer> q = HFactory.createSliceQuery(ko, se, se,IntegerSerializer.get());
			q.setColumnFamily("items")
				.setKey(Integer.toString(itemID))
				.setColumnNames("quantity");
			QueryResult<ColumnSlice<String, Integer>> r = q.execute();
			Integer itemQuantity=(r.get().getColumnByName("quantity").getValue());
			

			SliceQuery<String, String, String> q1 = HFactory.createSliceQuery(ko, se, se,se);
			q1.setColumnFamily("items")
				.setKey(Integer.toString(itemID))
				.setColumnNames("buyNowPrice");
			QueryResult<ColumnSlice<String, String>> r1 = q1.execute();
			
			Float buyNowPrice=Float.parseFloat(r1.get().getColumnByName("buyNowPrice").getValue());
			
			System.out.println(quantity);
			System.out.println(itemQuantity);
			
			if(itemQuantity>=quantity){
				Integer i = quantity-itemQuantity;
				System.out.println(i);
				Random generator = new Random();
				Integer curItem = new Integer(generator.nextInt());
				
				Mutator<String> mutator = HFactory.createMutator(ko, se);
				mutator.addInsertion(""+curItem, "bids",HFactory.createColumn("itemid",itemID, se, IntegerSerializer.get()));
				mutator.addInsertion(""+curItem, "bids",HFactory.createColumn("userid",userID, se, IntegerSerializer.get()));
				mutator.addInsertion(""+curItem, "bids", HFactory.createColumn("quantity",quantity, se, IntegerSerializer.get()));
				mutator.addInsertion(""+curItem, "bids", HFactory.createColumn("datetime", new Date().getTime(), se, LongSerializer.get()));
				mutator.addInsertion(""+curItem, "bids",HFactory.createStringColumn("curBid", ""+buyNowPrice));
				mutator.addInsertion(""+curItem, "bids",HFactory.createStringColumn("maxBid", ""+buyNowPrice));				
				mutator.addInsertion(""+itemID, "items", HFactory.createColumn("quantity", i.intValue(), se, IntegerSerializer.get()));
				mutator.execute();
				return true;
			}else{
				return false;
			}		
		}catch(Exception e){
			return false;
		}
	}
	
	
	
	public ArrayList<Item> getCategoryItems(int categoryID, int page, int itemsPP) throws Exception{
		ArrayList<Item> items = new ArrayList<Item>();
		try{
			QueryResult<OrderedRows<String, String, Integer>> result = null;
			String startKey = "";
			Row<String, String, Integer> rowData= null;
			for (int number = 0; number < page; number++) {
			
			IndexedSlicesQuery<String, String, Integer> indexedSlicesQuery =
					HFactory.createIndexedSlicesQuery(ko, se, se, IntegerSerializer.get());
					indexedSlicesQuery.addEqualsExpression("categoryID", categoryID);
					indexedSlicesQuery.setColumnNames("name");
					indexedSlicesQuery.setColumnFamily("items");
					indexedSlicesQuery.setStartKey(startKey);
					indexedSlicesQuery.setRowCount(itemsPP);
					result = indexedSlicesQuery.execute();
					Iterator<Row<String, String, Integer>>  temp = result.get().iterator();
					while(temp.hasNext()){
						rowData=temp.next();
						System.out.println(rowData.getKey());
					}
					
			}
				
				IndexedSlicesQuery<String, String, Integer> indexedSlicesQuery =
						HFactory.createIndexedSlicesQuery(ko, se, se, IntegerSerializer.get());
						indexedSlicesQuery.addEqualsExpression("categoryID", categoryID);
						indexedSlicesQuery.setColumnFamily("items");
						indexedSlicesQuery.setColumnNames("name");
						indexedSlicesQuery.setStartKey(startKey);
						indexedSlicesQuery.setRowCount(itemsPP);
						result = indexedSlicesQuery.execute();
						Iterator<Row<String, String, Integer>>  temp = result.get().iterator();
						while(temp.hasNext()){
							rowData=temp.next();
							SliceQuery<String, String, String> q = HFactory.createSliceQuery(ko, se, se,se);
							q.setColumnFamily("items")
								.setKey(rowData.getKey())
								.setColumnNames("name", "description","startPrice","reservePrice","buyNowPrice","currentBid","maxBid","noOfBids","thumbnail");
							QueryResult<ColumnSlice<String, String>> r = q.execute();
							
							SliceQuery<String, String, Integer> q1 = HFactory.createSliceQuery(ko, se, se,IntegerSerializer.get());
							q1.setColumnFamily("items")
								.setKey(rowData.getKey())
								.setColumnNames("quantity","sellerID","categoryID");
							QueryResult<ColumnSlice<String, Integer>> r1 = q1.execute();
							
							
							
							SliceQuery<String, String, Long> q2 = HFactory.createSliceQuery(ko, se, se,LongSerializer.get());
							q2.setColumnFamily("items")
								.setKey(rowData.getKey())
								.setColumnNames("startDate","endDate");
							QueryResult<ColumnSlice<String, Long>> r2 = q2.execute();
							
							@SuppressWarnings("deprecation")
							Item currentItem = new Item(Integer.parseInt(rowData.getKey().toString()),
									r.get().getColumnByName("name").getValue(),
									r.get().getColumnByName("description").getValue(),
									new Integer(r1.get().getColumnByName("quantity").getValue()),
									Float.parseFloat(r.get().getColumnByName("startPrice").getValue()),
									Float.parseFloat(r.get().getColumnByName("reservePrice").getValue()),
									Float.parseFloat(r.get().getColumnByName("buyNowPrice").getValue()),
									Float.parseFloat(r.get().getColumnByName("currentBid").getValue()),
									Float.parseFloat(r.get().getColumnByName("maxBid").getValue()),
									Integer.parseInt(r.get().getColumnByName("noOfBids").getValue()),
									new Date(r2.get().getColumnByName("startDate").getValue()),
									new Date(r2.get().getColumnByName("endDate").getValue()),
									new Integer(r1.get().getColumnByName("sellerID").getValue()),
									categoryID,
									r.get().getColumnByName("thumbnail").getValue(),
									new ArrayList<Image>());
							items.add(currentItem);
						}
		}
		catch(Exception e){
			System.err.println("Cassandra (getCategoryItems): Could not get the items");
			e.printStackTrace();
			throw e;
		}
		return items;
	}
	
	
	public boolean insertItem(int userID, String name, String description, float startPrice, float reservePrice, float buyNowPrice, int quantity, Date endDate, int categoryID){
		try{
			Mutator<String> mutator = HFactory.createMutator(ko, se);
			Random generator = new Random();
			Integer curItem = new Integer(generator.nextInt());
			System.out.println(curItem);
			mutator.addInsertion(""+curItem, "items", HFactory.createStringColumn("name", name))
					.addInsertion(""+curItem, "items", HFactory.createStringColumn("description", description))
					.addInsertion(""+curItem, "items", HFactory.createStringColumn("startPrice", ""+startPrice))
					.addInsertion(""+curItem, "items", HFactory.createStringColumn("reservePrice", ""+reservePrice))
					.addInsertion(""+curItem, "items", HFactory.createStringColumn("buyNowPrice", ""+buyNowPrice))
					.addInsertion(""+curItem, "items", HFactory.createColumn("quantity",quantity,se,IntegerSerializer.get()))
					.addInsertion(""+curItem, "items", HFactory.createColumn("startDate", new Date().getTime(),se,LongSerializer.get()))
					.addInsertion(""+curItem, "items", HFactory.createColumn("endDate",endDate.getTime(),se,LongSerializer.get()))
					.addInsertion(""+curItem, "items",  HFactory.createStringColumn("maxBid", ""+0))
					.addInsertion(""+curItem, "items",  HFactory.createStringColumn("currentBid", ""+0))
					.addInsertion(""+curItem, "items",  HFactory.createStringColumn("noOfBids", ""+0))
					.addInsertion(""+curItem, "items",  HFactory.createStringColumn("thumbnail", ""))
					.addInsertion(""+curItem, "items", HFactory.createColumn("sellerID",userID,se,IntegerSerializer.get()))
					.addInsertion(""+curItem, "items", HFactory.createColumn("categoryID", categoryID,se,IntegerSerializer.get()));
			mutator.execute();
			}catch(Exception e){System.out.println(e.getMessage());return false;}
		return true;
	}
	
	public boolean insertUser(String username, String password, String email, String firstName, String lastName){
		try{
			Mutator<String> mutator = HFactory.createMutator(ko, se);
			Random generator = new Random();
			Integer curItem = new Integer(Math.abs(generator.nextInt()));
			
			mutator.addInsertion(username, "users", HFactory.createColumn("userid",curItem,se,IntegerSerializer.get()))
					.addInsertion(username, "users", HFactory.createStringColumn("password", password))
					.addInsertion(username, "users", HFactory.createStringColumn("email", email))
					.addInsertion(username, "users", HFactory.createStringColumn("firstname", firstName))
					.addInsertion(username, "users", HFactory.createStringColumn("lastname", lastName));
			mutator.execute();
			
			}catch(Exception e){System.out.println(e.getMessage());return false;}
		return true;
	}
	

	public String makeNewAuthToken(int userID){
		String newAuthToken = authToken(userID);
		if(newAuthToken != null){
			try{
			Mutator<String> mutator = HFactory.createMutator(ko, se);
			mutator.insert(""+userID, "auth", HFactory.createStringColumn("authtoken", newAuthToken));
			}catch(Exception e){return e.getMessage();}
		}
		return newAuthToken;
	}
	
	public int checkUsernamePassword(String username, String password){
		if(username!=null){
			try{
				SliceQuery<String, String,String> q = HFactory.createSliceQuery(ko, se, se, se);
				q.setColumnFamily("users");
				q.setKey(username);
				q.setColumnNames("password");
				QueryResult<ColumnSlice<String,String>> result = q.execute();
				if(result.get().getColumnByName("password").getValue().equals(password)){
					SliceQuery<String, String,Integer> q1 = HFactory.createSliceQuery(ko, se, se, IntegerSerializer.get());
					q1.setColumnFamily("users");
					q1.setKey(username);
					q1.setColumnNames("userid");
					QueryResult<ColumnSlice<String,Integer>> result1 = q1.execute();
					System.out.println("User ("+result1.get().getColumnByName("userid").getValue()+") Authenticated Successfully");
					return result1.get().getColumnByName("userid").getValue().intValue();
				}else{
					System.out.println("User/Password Not Authenticated.");
					return -1;
				}
			}catch(Exception e){System.out.print(e.getMessage());return -1;}
			
		}else{
			System.out.println("Username not entered.");
			return -1;
		}
	}
	
	

	
	
	
	
	public boolean checkAuthToken(int userID, String authToken){
		/*
		if(username!=null){
			try{
				IndexedSlicesQuery<String, String, Integer> indexedSlicesQuery =
						HFactory.createIndexedSlicesQuery(ko, se, se, IntegerSerializer.get());
						indexedSlicesQuery.addEqualsExpression("userID", userID);
						indexedSlicesQuery.setColumnFamily("users");
						indexedSlicesQuery.setColumnNames("userid");
						indexedSlicesQuery.setStartKey("");
						indexedSlicesQuery.setRowCount(1);
				QueryResult<OrderedRows<String, String, Integer>> result = indexedSlicesQuery.execute();
				Row<String,String,Integer> temp =result.get().iterator().next();
				System.out.print
						
				SliceQuery<String, String, Integer> q = HFactory.createSliceQuery(ko, se, se,IntegerSerializer.get());
				q.setColumnFamily("users")
					.setKey("")
					.setColumnNames("userid")
					.
				QueryResult<ColumnSlice<String, Integer>> r = q.execute();
				Integer itemQuantity=(r.get().getColumnByName("userid").getValue());
				

				SliceQuery<String, String, String> q1 = HFactory.createSliceQuery(ko, se, se,se);
				q1.setColumnFamily("users")
					.setKey(Integer.toString(itemID))
					.setColumnNames("buyNowPrice");
				QueryResult<ColumnSlice<String, String>> r1 = q1.execute();
				*/
		return true;
	}
	
	public Date getItemEndDate(int itemID){
		
		SliceQuery<String, String, Long> q = HFactory.createSliceQuery(ko, se, se,LongSerializer.get());
		q.setColumnFamily("items")
			.setKey(Integer.toString(itemID))
			.setColumnNames("endDate");
		QueryResult<ColumnSlice<String, Long>> r = q.execute();
		System.out.println(r.get().getColumnByName("endDate").getValue());
		Date endDate=new Date(r.get().getColumnByName("endDate").getValue());
		
		return endDate;
	}
	
	public float getCurrentBid(int itemID){
		SliceQuery<String, String, String> q = HFactory.createSliceQuery(ko, se, se,StringSerializer.get());
		q.setColumnFamily("items")
			.setKey(Integer.toString(itemID))
			.setColumnNames("currentBid");
		QueryResult<ColumnSlice<String, String>> r = q.execute();
		System.out.println(r.get().getColumnByName("currentBid").getValue());
		Float currentBid=new Float(r.get().getColumnByName("currentBid").getValue());
		
		return currentBid;
	}
	
	//TODO: maybe we should read this and current together since they'll both be checked
	public float getMaxBid(int itemID){
		SliceQuery<String, String, String> q = HFactory.createSliceQuery(ko, se, se,StringSerializer.get());
		q.setColumnFamily("items")
			.setKey(Integer.toString(itemID))
			.setColumnNames("maxBid");
		QueryResult<ColumnSlice<String, String>> r = q.execute();
		System.out.println(r.get().getColumnByName("maxBid").getValue());
		Float maxBid=new Float(r.get().getColumnByName("maxBid").getValue());
		
		return maxBid;
	}
	
	public int getQuantity(int itemID){
		SliceQuery<String, String, Integer> q = HFactory.createSliceQuery(ko, se, se,IntegerSerializer.get());
		q.setColumnFamily("items")
			.setKey(Integer.toString(itemID))
			.setColumnNames("quantity");
		QueryResult<ColumnSlice<String, Integer>> r = q.execute();
		System.out.println(r.get().getColumnByName("quantity").getValue());
		Integer quantity=new Integer(r.get().getColumnByName("quantity").getValue());
		
		return quantity;
	}
	
	public Item getItem(int itemID, Boolean getImages){
		SliceQuery<String, String, String> q = HFactory.createSliceQuery(ko, se, se,se);
		q.setColumnFamily("items")
			.setKey(Integer.toString(itemID))
			.setColumnNames("name", "description","startPrice","reservePrice","buyNowPrice","currentBid","maxBid","noOfBids","thumbnail");
		QueryResult<ColumnSlice<String, String>> r = q.execute();
		
		SliceQuery<String, String, Integer> q1 = HFactory.createSliceQuery(ko, se, se,IntegerSerializer.get());
		q1.setColumnFamily("items")
			.setKey(Integer.toString(itemID))
			.setColumnNames("quantity","sellerID","categoryID");
		QueryResult<ColumnSlice<String, Integer>> r1 = q1.execute();
		
		
		
		SliceQuery<String, String, Long> q2 = HFactory.createSliceQuery(ko, se, se,LongSerializer.get());
		q2.setColumnFamily("items")
			.setKey(Integer.toString(itemID))
			.setColumnNames("startDate","endDate");
		QueryResult<ColumnSlice<String, Long>> r2 = q2.execute();
		
		@SuppressWarnings("deprecation")
		Item currentItem = new Item(itemID,
				r.get().getColumnByName("name").getValue(),
				r.get().getColumnByName("description").getValue(),
				new Integer(r1.get().getColumnByName("quantity").getValue()),
				Float.parseFloat(r.get().getColumnByName("startPrice").getValue()),
				Float.parseFloat(r.get().getColumnByName("reservePrice").getValue()),
				Float.parseFloat(r.get().getColumnByName("buyNowPrice").getValue()),
				Float.parseFloat(r.get().getColumnByName("currentBid").getValue()),
				Float.parseFloat(r.get().getColumnByName("maxBid").getValue()),
				Integer.parseInt(r.get().getColumnByName("noOfBids").getValue()),
				new Date(r2.get().getColumnByName("startDate").getValue()),
				new Date(r2.get().getColumnByName("endDate").getValue()),
				new Integer(r1.get().getColumnByName("sellerID").getValue()),
				new Integer(r1.get().getColumnByName("categoryID").getValue()),
				r.get().getColumnByName("thumbnail").getValue(),
				new ArrayList<Image>());
		return currentItem;
	}
	
	public String getFirstName(int userID){
		String name = null;
		
		SliceQuery<String, String, String> q = HFactory.createSliceQuery(ko, se, se,se);
		q.setColumnFamily("users")
			.setKey(Integer.toString(userID))
			.setColumnNames("firstname");
		QueryResult<ColumnSlice<String, String>> r = q.execute();
		name=r.get().getColumnByName("firstname").toString();
		return name;
	}
	
	public ArrayList<Image> getItemImages(int itemID){
		
		ArrayList<Image> images = new ArrayList<Image>();

		
		return images;
	}
	
	public ArrayList<Bid> getCurrentBids(int userID){
		return this.getXBids(userID, "bids", "items");
	}
	
	public ArrayList<Bid> getOldBids(int userID){
		return this.getXBids(userID, "oldBids", "oldItems");
	}
	
	
	
	
	/*
	public void insertBid(int userID, int itemID, int quantity, float bid, float maxBid){
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				/*
				 * 1. For consistent budding we need to make sure no one enters a lower-maxBid when we are writing
				 * 		- otherwise they may beat the current max bid and write that they are winning
				 * 		- but they didn't beat the bid that we are halfway through writing
				 * 2. So we must stop people updating the item or entering a bid while we are updating the item
				 * 3. Whoever is winning the bidding now must have the current largest 'maxBid'
				 * 		- The current bed bid is the lowest maxBid+0.01, or just our bid if we bid more than the previous maxBid
				 * 4. The items new price will be the smallest of the 'maxBid' prices plus one cent, or the bid value
				 * 		- unless both maxBids are the same, then the bid will be a draw and the price will be maxBid
				 * 		- (we know that the bid is higher than the currentBid or this method should not have been called)
				 * 5. We'll also update the number of bids value no matter what happens
				 * 6. Lastly, just in case there was some out of order update, we'll make sure the new bids are greater than the old ones
				 
				
				// SQL to lock the items table
				PreparedStatement lockTableStatement = conn.prepareStatement("LOCK TABLES items WRITE");
				
				// Get the item's currentBid and maxBid values (and noOfBids)
				PreparedStatement getMaxBidStatement = conn.prepareStatement("SELECT currentBid, maxBid, noOfBids, currentWinner FROM `items` FORCE INDEX (`PRIMARY`)" +
						"WHERE `items`.`id` = ?");
				getMaxBidStatement.setInt(1, itemID);
				
				// SQL to update the item with the new bids
				PreparedStatement updateItemStatement = conn.prepareStatement("UPDATE `items` SET `items`.`currentBid` = ?, `items`.`maxBid` = ?, `items`.`noOfBids` = ?, `items`.`currentWinner` = ? WHERE `items`.`id` = ?");
				updateItemStatement.setInt(5,itemID);
				
				// Unlock the tables
				PreparedStatement unlockTableStatement = conn.prepareStatement("UNLOCK TABLES");
				
				// SQL to enter the user's bid
				PreparedStatement insertBidStatement = conn.prepareStatement("INSERT INTO `bids` (userID, itemID, quantity, bid, maxBid, bidDate) "
						+ "VALUES (?, ?, ?, ?, ?, NOW())");
				insertBidStatement.setInt(1, userID);
				insertBidStatement.setInt(2, itemID);
				insertBidStatement.setInt(3, quantity);
				insertBidStatement.setFloat(4, bid);
				insertBidStatement.setFloat(5, maxBid);
				
				//TODO: Andy: I feel like we may still get a sync issue here? maybe not if they'll all wait for the lock...
				//UPDATE: Andy: I actually think this is fine. anyone else have thoughts?
				lockTableStatement.executeUpdate();
				
				insertBidStatement.executeUpdate();
				insertBidStatement.close();
				
				ResultSet rs = getMaxBidStatement.executeQuery();	
				
				if(rs.next()){
					float dbCurrentBid = rs.getFloat("currentBid");
					float dbMaxBid = rs.getFloat("maxBid");
					int dbNoOfBids = rs.getInt("noOfBids");
					int currentWinner = rs.getInt("currentWinner");
					
					if(dbMaxBid==maxBid){
						// New max bid is the same as old max bid, old user is still winning as they bidded first
						updateItemStatement.setFloat(1,dbMaxBid);
						updateItemStatement.setFloat(2,dbMaxBid);
						updateItemStatement.setInt(3,dbNoOfBids+1);
						updateItemStatement.setInt(4,currentWinner);
						
					}
					else if(dbMaxBid>maxBid){
						// The old max bid is greater, old user still winning by 0.01c
						// max bid is the same, current bid is the new bid's maxPrice+1c
						updateItemStatement.setFloat(1,maxBid + 0.01f);
						updateItemStatement.setFloat(2,dbMaxBid);
						updateItemStatement.setInt(3,dbNoOfBids+1);
						updateItemStatement.setInt(4,currentWinner);
					}
					else{
						// Else the new bid is more, the new user is winning
						// The new user's bid will be the old user's max(maxBid+1c, new bid)
						updateItemStatement.setFloat(1,(bid>dbMaxBid) ? bid : new Float(dbMaxBid + 0.01f));
						updateItemStatement.setFloat(2,maxBid);
						updateItemStatement.setInt(3,dbNoOfBids+1);
						updateItemStatement.setInt(4,userID);
					}
					
					// Update the item table
					
					
					// These should not be needed as long as there are no out of order bids
					// We could also move the setting of the variables in the if-else structure. But I did it the
					// long way for clarity
					if(dbCurrentBid > newCurrentBid) newCurrentBid = dbCurrentBid;
					if(dbMaxBid > newMaxBid) newMaxBid = dbMaxBid;
					
					updateItemStatement.setFloat(1,newCurrentBid);
					updateItemStatement.setFloat(2,newMaxBid);
					updateItemStatement.setInt(3,dbNoOfBids+1);
					updateItemStatement.setInt(4,newWinningUser);
					
					updateItemStatement.executeUpdate();
				}
				unlockTableStatement.executeUpdate();
				unlockTableStatement.close();
				
				lockTableStatement.close();
				getBidsStatement.close();
				
				// Inserting the bid does not need to be donw with the lock as the info used to compare is held in the item
				insertBidStatement.executeUpdate();
				insertBidStatement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (insertBid): Could not get insert bid");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
	}*/
	
	public ArrayList<Item> getCurrentSellingItems(int userID){
		return this.getXSellingItems(userID, "items");
	}
	
	public ArrayList<Item> getOldSellingItems(int userID){
		return this.getXSellingItems(userID, "oldItems");
	}
	
	protected ArrayList<Item> getXSellingItems(int userID, String table){
		ArrayList<Item> items = new ArrayList<Item>();
		QueryResult<OrderedRows<String, String, Integer>> result = null;
		String startKey = "";
		Row<String, String, Integer> rowData= null;		
		IndexedSlicesQuery<String, String, Integer> indexedSlicesQuery =
				HFactory.createIndexedSlicesQuery(ko, se, se, IntegerSerializer.get());
				indexedSlicesQuery.addEqualsExpression("userID", userID);
				indexedSlicesQuery.setColumnNames("quantity");
				indexedSlicesQuery.setColumnFamily("items");
				indexedSlicesQuery.setStartKey(startKey);
				result = indexedSlicesQuery.execute();
				Iterator<Row<String, String, Integer>>  temp = result.get().iterator();
				while(temp.hasNext()){
					rowData=temp.next();
					String itemID=rowData.getKey();
					Item curItem = this.getItem(Integer.getInteger(itemID), false);
					items.add(curItem);
				}
		return items;
	}
	
	public ArrayList<Item> getTextItems(String text){
		ArrayList<Item> items = new ArrayList<Item>();
		
		
		return items;
	}
	
	
	
	//TODO: This is going to crash if an 'oldBid' is for a new item, or current item has bids in oldBids
	// May return some expired bids if they have not been moved to 'oldBids'
	private ArrayList<Bid> getXBids(int userID, String table, String itemTable){
		ArrayList<Bid> bids = new ArrayList<Bid>();
		
		QueryResult<OrderedRows<String, String, Integer>> result = null;
		String startKey = "";
		Row<String, String, Integer> rowData= null;		
		IndexedSlicesQuery<String, String, Integer> indexedSlicesQuery =
				HFactory.createIndexedSlicesQuery(ko, se, se, IntegerSerializer.get());
				indexedSlicesQuery.addEqualsExpression("userID", userID);
				indexedSlicesQuery.setColumnNames("quantity");
				indexedSlicesQuery.setColumnFamily("bids");
				indexedSlicesQuery.setStartKey(startKey);
				result = indexedSlicesQuery.execute();
				Iterator<Row<String, String, Integer>>  temp = result.get().iterator();
				while(temp.hasNext()){
					rowData=temp.next();
					String bidID=rowData.getKey();

					SliceQuery<String, String, String> q = HFactory.createSliceQuery(ko, se, se,se);
					q.setColumnFamily("bids")
						.setKey(bidID)
						.setColumnNames("curBid","maxBid");
					QueryResult<ColumnSlice<String, String>> r = q.execute();
					
					SliceQuery<String, String, Integer> q1 = HFactory.createSliceQuery(ko, se, se,IntegerSerializer.get());
					q1.setColumnFamily("bids")
						.setKey(bidID)
						.setColumnNames("itemID","quantity");
					QueryResult<ColumnSlice<String, Integer>> r1 = q1.execute();
					
					SliceQuery<String, String, Long> q2 = HFactory.createSliceQuery(ko, se, se,LongSerializer.get());
					q2.setColumnFamily("bids")
						.setKey(bidID)
						.setColumnNames("datetime");
					QueryResult<ColumnSlice<String, Long>> r2 = q2.execute();
					
					@SuppressWarnings("deprecation")
					Bid currentBid = new Bid(r1.get().getColumnByName("quantity").getValue(),
							Float.valueOf(r.get().getColumnByName("curBid").getValue()),
							Float.valueOf(r.get().getColumnByName("maxBid").getValue()),
							new Date(r2.get().getColumnByName("datetime").getValue()),
							this.getItem(r1.get().getColumnByName("itemID").getValue(), false));
							
					bids.add(currentBid);
					}
		
		return bids;
	}
	
	//TODO: make create items methods?
	
	public ArrayList<Category> getCategories(int parent) throws Exception{
		ArrayList<Category> categories = new ArrayList<Category>();
		/*
		int attemptsRemaining = 5;
		
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					PreparedStatement statement = conn.prepareStatement("SELECT * FROM `categories` WHERE parent = ?");
					statement.setInt(1, parent);
					ResultSet rs = statement.executeQuery();
					
					// Put all of the states in to the results vector
					while(rs.next()){
						Category category = new Category(rs.getInt("categories.id"),
								rs.getString("categories.name"),
								rs.getInt("categories.parent"));
						
						categories.add(category);
					}
					
					rs.close();
					statement.close();
					attemptsRemaining=0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (getCategories): Could not get the categories");
					e.printStackTrace();
					throw e;
				}
				
				this.closeConnection(conn);	
			}
			
			attemptsRemaining--;
		}while(attemptsRemaining>0);
			*/
		return categories;
	}
	
	public ArrayList<Category> getAllCategories() throws Exception{
		ArrayList<Category> categories = new ArrayList<Category>();
		for(int i=1;i<6;i++){
			Category category = new Category(i,
					"Name"+i,
					0);
			
			categories.add(category);
		}
		return categories;
	}
	
	public Category getCategory(int categoryID) throws Exception{
		Category category = new Category(categoryID,
				"Name"+categoryID,
				0);
		
		return category;
	}
	
	public boolean insertAddress(int userID, String street, String town, String zip, int state, Boolean isDefault){
		boolean insertedAddress = false;
		/*
		int attemptsRemaining = 5;
		//TODO: fail if nulls passed
	
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					// Create the SQL statement to insert the item
					PreparedStatement statement = conn.prepareStatement("INSERT INTO `addresses` (userID, street, town, zip, state, isDefault) "
											+ "VALUES (?, ?, ?, ?, ?, ?)");
					statement.setInt(1, userID);
					statement.setString(2, street);
					statement.setString(3, town);
					statement.setString(4, zip);
					statement.setInt(5, state);
					statement.setBoolean(6, isDefault);
					
					statement.executeUpdate();
					statement.close();
					
					insertedAddress = true;
					attemptsRemaining = 0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (insertAddress): Could not insert address");
					e.printStackTrace();
					
					//TODO: remove bad info
					
					insertedAddress = false;
				}
				
				this.closeConnection(conn);
			}
		
			attemptsRemaining--;
		}while(attemptsRemaining > 0);
		*/
		return insertedAddress;
	}
	
	public boolean updateAddress(int id, int userID, String street, String town, String zip, int state, Boolean isDefault){
		boolean updatedAddress = false;
		/*
		int attemptsRemaining = 5;
		//TODO: fail if nulls passed
	
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					// Create the SQL statement to insert the item
					PreparedStatement statement = conn.prepareStatement("UPDATE `addresses` set street = ?, town = ?, zip = ?, state = ?, isDefault = ? WHERE id = ? AND userID = ?");

					statement.setString(1, street);
					statement.setString(2, town);
					statement.setString(3, zip);
					statement.setInt(4, state);
					statement.setBoolean(5, isDefault);
					
					// Must use id ans userID for security
					statement.setInt(6, id);
					statement.setInt(7, userID);
					
					
					statement.executeUpdate();
					statement.close();
					
					updatedAddress = true;
					attemptsRemaining = 0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (updateAddress): Could not update address");
					e.printStackTrace();
					
					//TODO: remove bad info
					
					updatedAddress = false;
				}
				
				this.closeConnection(conn);
			}
		
			attemptsRemaining--;
		}while(attemptsRemaining > 0);
		*/
		return updatedAddress;
	}
	
	
	
	public ArrayList<Address> getAddresses(int userID){
		return this.getAddresses(userID, Boolean.FALSE);
	}
	
	private ArrayList<Address> getAddresses(int userID, Boolean onlyDefault){
		ArrayList<Address> addresses = new ArrayList<Address>();

		
		return addresses;
	}
	
	public User getUser(int userID){
		User user = null;
		
		QueryResult<OrderedRows<String, String, Integer>> result = null;
		String startKey = "";
		Row<String, String, Integer> rowData= null;		
		IndexedSlicesQuery<String, String, Integer> indexedSlicesQuery =
				HFactory.createIndexedSlicesQuery(ko, se, se, IntegerSerializer.get());
				indexedSlicesQuery.addEqualsExpression("userID", userID);
				indexedSlicesQuery.setColumnNames("userID");
				indexedSlicesQuery.setColumnFamily("users");
				indexedSlicesQuery.setStartKey("");
				result = indexedSlicesQuery.execute();
				System.out.println(result.get().getList().size());
				Iterator<Row<String, String, Integer>>  temp = result.get().iterator();
				while(temp.hasNext()){
					rowData=temp.next();
					String username=rowData.getKey();
					System.out.println(username);
					SliceQuery<String, String, String> q = HFactory.createSliceQuery(ko, se, se,se);
					q.setColumnFamily("users")
						.setKey(username)
						.setColumnNames("password","email","username","firstname","lastname");
					QueryResult<ColumnSlice<String, String>> r = q.execute();
					System.out.println(r.get().getColumnByName("firstname").getValue());
					
					user = new User(userID,
										r.get().getColumnByName("firstname").getValue(),
										r.get().getColumnByName("lastname").getValue(),
										r.get().getColumnByName("username").getValue(),
										r.get().getColumnByName("password").getValue(),
										r.get().getColumnByName("email").getValue(),
										"");
				}
		return user;
	}
	
	public boolean emailExists(String email){
		/*
		Connection conn = this.getConnection();
		boolean exists = false;
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the email address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT `email` FROM `users` WHERE `users`.`email` = ?");
				statement.setString(1, email);
				
				ResultSet rs = statement.executeQuery();
				
				// If the e-mail address does exist in the table
				if(rs.next()){
					exists = true;
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (checkEmailExists): Could not read results set");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return exists;
		*/
		return false;
	}
	
	public boolean usernameExists(String username){
		/*
		Connection conn = this.getConnection();
		boolean exists = false;
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the username address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT `username` FROM `users` WHERE `users`.`username` = ?");
				statement.setString(1, username);
				
				ResultSet rs = statement.executeQuery();
				
				// If the username does exist in the table
				if(rs.next()){
					exists = true;
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (checkUsernameExists): Could not read results set");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return exists;
		*/
		return false;
	}
	
	public ArrayList<String[]> getStates() throws Exception{
		ArrayList<String[]> states = new ArrayList<String[]>();
		/*
		int attemptsRemaining = 5;
		
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					PreparedStatement statement = conn.prepareStatement("SELECT * FROM `states`");
					ResultSet rs = statement.executeQuery();
					
					// Put all of the states in to the results vector
					while(rs.next()){
						String[] result = new String[3];
						result[0] = rs.getString("id");
						result[1] = rs.getString("short");
						result[2] = rs.getString("long");
						
						states.add(result);
					}
					
					rs.close();
					statement.close();
					
					attemptsRemaining = 0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (getStates): Could not read results set");
					e.printStackTrace();
					throw e;
				}
				
				this.closeConnection(conn);
			}
		
			attemptsRemaining--;
		}while(attemptsRemaining>0);
		*/
		return states;
	}

	@Override
	protected Connection initConnection() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected Connection getConnection() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected void closeConnection(Connection conn) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Address getDefaultAddress(int userID) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public ArrayList<Bid> getCurrentBidsTime(int parseInt, Date parse) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public ArrayList<Item> getCurrentSellingItemsTime(int parseInt, Date parse) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public ArrayList<Bid> getOldBidsTime(int parseInt, Date parse) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public ArrayList<Item> getOldSellingItemsTime(int parseInt, Date parse) {
		// TODO Auto-generated method stub
		return null;
	}
}
